DB-Design-Finals-Answers
Tags: #◇CCINFOM #◇Data Modeling #◇Information Structures #◇Computer Science #◇Databases #◇SQL
◇Overview
Subject: Relational Database Design & ERD Modeling
◇Details / Theory
DB Design Finals Mock Exam - ANSWERS
Subject: Relational Database Design & ERD Modeling
Visual ERDs are rendered natively below using interactive Mermaid entity-relationship diagrams.
◇Answer 1: The Public Library
Logic:
Title vs. Copy: This is a classic design pattern. Title holds the metadata (Name, ISBN). Copy holds the physical item data (Barcode, Condition). One Title has Many Copies. Authors: M:N relationship with Titles. Requires Title_Author junction table. Loans: A Patron borrows a Copy, not a Title. This is a M:N relationship between Patron and Copy. Since we need history, we keep all records.
Entity-Relationship Diagram:
◇Answer 2: Employee Project Management
Logic:
Department Manager: This is a 1:1 relationship between Employee and Department (one employee manages one dept). But also a 1:M (one dept has many employees). These are two separate relationships between the same two tables. Dependents: A Weak Entity. It relies on Employee for existence. Its PK is usually (EmpID, DependentName). Assignments: M:N between Employee and Project. The HoursWorked and Role are attributes of this relationship.
Entity-Relationship Diagram:
◇Answer 3: E-Commerce with Split Shipments
Logic:
The Trap: You might try to link Shipment directly to Product. But you need to know which specific order line is being shipped to track if an order is fully fulfilled. The Solution: Shipment is a child of Order. ShipmentItem is a child of Shipment AND links to Product (or OrderDetail). Best Practice: Link ShipmentItem to OrderDetail. This ensures you can't ship an item that wasn't ordered.
Entity-Relationship Diagram:
◇Answer 4: University Hospital System (Expert)
Logic:
Inheritance (Supertype/Subtype): Person is the Supertype. Staff and Patient are Subtypes. Staff is also a Supertype for Doctor and Nurse.
Implementation: The Subtypes share the same Primary Key as the Supertype (1:1 relationship).
Ternary Relationship: A prescription involves a Doctor, a Patient, and a Drug. We model this as an Associative Entity (Prescription) with three Foreign Keys.
Entity-Relationship Diagram:
◇Common Pitfalls
Comparing your ERD structure without checking cardinality and participation constraints. Overlooking the Title vs. Copy distinction in the library scenario — one Title can have many Copies. Forgetting ShipmentItem as a junction entity in the e-commerce split-shipment design.
◇Summary
TL;DR — PlantUML ERD solutions for the four DB-Design mock problems, showing correct entity identification, junction tables, cardinality/participation, and EER inheritance patterns.
◇References & Navigation
- Related Notes: ◇Database Design
ACTIVE RECALL VERIFICATION
Select an answer to verify conceptual comprehension and reveal the technical rationale.
In the library database design scenario, why is it critical to model Title and Copy as separate entities rather than storing copies as an integer quantity column inside Title?
When modeling an e-commerce order fulfillment system that allows split shipments across multiple dates, which entity relationship structure correctly resolves the fulfillment tracking?
In the hospital prescription model linking Doctor, Patient, and Medication, why is Prescription represented as a ternary association rather than two independent binary relationships (Doctor-Patient and Patient-Medication)?
In Extended Entity-Relationship (EER) modeling, what is the meaning of a disjointness constraint () applied to the specialization of Person into Staff and Patient?
In database normalization, if a composite primary key determines , but independently determines , what normal form violation exists and how is it resolved?